package com.eece496.Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.eece496.Helpers.COGSInfo;
import com.eece496.Helpers.TADutiesInfo;

public class TAduties extends Database {

	private int TAID; 
	private String COGSName; 
	private String division;
	private int studentID;
	private String groupNumber;
	private String question; 
	private String room; 
	private String StartTime; 
	private String EndTime; 
	private String courseNumber; 
	private String sectionNumber; 
	private String status;
	
	public TAduties(){
		tableName = "TAduties";
	}
	
	public TAduties(int TAID, String division, String COGSName, int studentID, Integer groupNumber, String question, String room, String StartTime, String EndTime, 
			String courseNumber, String sectionNumber,String status){
		tableName = "TAduties";
		this.TAID = TAID;
		this.division = division;
		this.COGSName = COGSName;
		this.studentID = studentID;
		this.groupNumber = Integer.toString(groupNumber);
		this.question = question;
		this.room = room;
		this.StartTime = StartTime;
		this.EndTime = EndTime;
		this.courseNumber = courseNumber;
		this.sectionNumber = sectionNumber;
		this.status = status;
	}
	
	@Override
	public void Insert() throws SQLException {
		
		Statement stmt = connection.createStatement();
		
	    String sqlString = "INSERT INTO " + tableName + " VALUES (" +
	    					TAID + ", " +			
	    					AddQuotes(division) + ", " +
	    					AddQuotes(COGSName) + ", " +
	    					studentID + ", " +	
	    					AddQuotes(groupNumber) + ", " +
	    					AddQuotes(question) + ", " +
	    					AddQuotes(room) + ", " +
	    					AddQuotes(StartTime) + ", " +
	    					AddQuotes(EndTime) + ", " +
	    					AddQuotes(courseNumber) + ", " +
	    					AddQuotes(sectionNumber) + ", " +
	    					AddQuotes(status) + ")";
	    
	    stmt.executeUpdate(sqlString);
	    System.out.println(sqlString);

	}

	
	public void DeleteCourseDuties(String courseNumber, String sectionNumber) throws SQLException{
		
		Statement stmt = connection.createStatement();
		String sqlString = "DELETE from " + tableName + " WHERE courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber= " + AddQuotes(sectionNumber);
		stmt.executeUpdate(sqlString);
		
	}
	
	public void DeleteStudentDuty(int studentID, String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();
		
		String sqlString = "DELETE FROM " + tableName + " WHERE " +
		" studentID = " + studentID + " and courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		
		stmt.executeUpdate(sqlString);
	}

	public void DeleteTADuty(int tID, String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();
		
		String sqlString = "DELETE FROM " + tableName + " WHERE " +
		" TAID = " + tID + " and courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		
		stmt.executeUpdate(sqlString);
	}
	
	@Override
	public void Delete(String primaryKey) throws SQLException {
		// TODO Auto-generated method stub

	}

	public void ModifyStatus(String status, String courseNumber, String sectionNumber, int TAID, String division, String COGSName) throws SQLException{
		
		Statement stmt = connection.createStatement();
		String sqlString = "UPDATE " + tableName + " SET " + "status" + " = " + AddQuotes(status) + " WHERE courseNumber = " + AddQuotes(courseNumber)
				+ " and sectionNumber= " + AddQuotes(sectionNumber) + " and division = " +
				AddQuotes(division) + " and COGSName = " + AddQuotes(COGSName) + " and TAID = " + TAID;
		stmt.executeUpdate(sqlString);
		System.out.println(sqlString + " complete");	
	}
	
	@Override
	public void Display() throws SQLException {
		// TODO Auto-generated method stub

	}
	
	public int getSize() throws SQLException{
		int count =0;
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + tableName;
		ResultSet rs = stmt.executeQuery(sqlString);
		while(rs.next())
		{
			count++;
		}
		return count;
	}
	
	public boolean UserExists(int studentID, String courseNumber, String sectionNumber ) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + tableName + " where studentID = " + studentID + " and courseNumber = " +
		AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		if (rs.next())//check if user exists in databse
		{
			int sid = rs.getInt("studentID");
			return true;
		}
		else
		{
			return false;
		}	
	}
	
	public ArrayList<String> GetGroupNumbers(int TAID, String COGSName, String courseNumber, String sectionNumber, String division) throws SQLException
	{
		ArrayList <String> list = new ArrayList<String>();
		Statement stmt = connection.createStatement();

		String sqlString = "select groupNumber from TADuties where TAID = " + TAID +  " and courseNumber = " +AddQuotes(courseNumber) +
				"and COGSName = " + AddQuotes(COGSName) + " and sectionNumber = " + AddQuotes(sectionNumber) + " and division = " +
				AddQuotes(division) + " order by StartTime ASC, EndTime ASC";
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			list.add(rs.getString("groupNumber"));
		//	System.out.println(rs.getInt("studentID"));
		}
		
		return list;
	}
	
	public ArrayList<Integer> GetStudentIDs(int TAID, String COGSName, String courseNumber, String sectionNumber, String division) throws SQLException
	{
		ArrayList<Integer> list = new ArrayList<Integer>();
		System.out.println(TAID);
		System.out.println(COGSName);
		System.out.println(courseNumber);
		System.out.println(sectionNumber);
		Statement stmt = connection.createStatement();
		
		String sqlString = "select studentID from TADuties where TAID = " + TAID +  " and courseNumber = " +AddQuotes(courseNumber) +
				"and COGSName = " + AddQuotes(COGSName) + " and sectionNumber = " + AddQuotes(sectionNumber) + " and division = " +
				AddQuotes(division) + " order by StartTime ASC, EndTime ASC";
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			list.add(rs.getInt("studentID"));
			System.out.println(rs.getInt("studentID"));
		}
		
		return list;
		
	}
	public ArrayList<String> GetCOGSNames(String courseNumber, String sectionNumber) throws SQLException{
		
		ArrayList<String> list = new ArrayList<String>();
		Statement stmt = connection.createStatement();
		
		String sqlString = "select COGSName from TADuties where courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber = " + AddQuotes(sectionNumber) + " group by COGSName";
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			list.add(rs.getString("COGSName"));
		}
		return list;
		
	}
	
	public ArrayList<TADutiesInfo>GetStudentList(int TAID, String courseNumber, String sectionNumber, String COGSName) throws SQLException{
	
		System.out.println(TAID);
		System.out.println(courseNumber);
		System.out.println(sectionNumber);
		System.out.println(COGSName);
		
		ArrayList<TADutiesInfo> list = new ArrayList<TADutiesInfo>();
		
		Statement stmt = connection.createStatement();
		
		String sqlString = "select Student.firstname, Student.lastname, Student.studentID,  " + " TAduties.groupNumber, " +
				"TAduties.question, TAduties.room, TAduties.StartTime, TAduties.EndTime from TAduties inner join " +
				" Student on TAduties.studentID = Student.studentID " +
						 " where courseNumber = " + AddQuotes(courseNumber) +
						 " and TAID = " + TAID +
						 " and sectionNumber = " + AddQuotes(sectionNumber) + " and COGSName = " + AddQuotes(COGSName) + " order by StartTime ASC "+
						 " , EndTime ASC";
				
		System.out.println(sqlString);
		ResultSet rs = stmt.executeQuery(sqlString);
		
		
		while (rs.next()) {
			TADutiesInfo info = new TADutiesInfo();
			info.studentID = rs.getInt("studentID");
			info.firstname = rs.getString("firstname");
			info.lastname = rs.getString("lastname");
			info.groupnumber = rs.getString("groupNumber");
			info.question = rs.getString("question");
			info.rooM = rs.getString("room");
			info.starttime = rs.getString("startTime");
			info.endtime = rs.getString("endTime");
			list.add(info);
		}
		
		System.out.println(list.size());
		return list;
	
	}
	
	public ArrayList<TADutiesInfo> GetAllTADutiesByDivision(String courseNumber, String sectionNumber, String division) throws SQLException{
		
		ArrayList<TADutiesInfo> list = new ArrayList<TADutiesInfo>();
		
		Statement stmt = connection.createStatement();

		//Grab all the COGSNames
		String sqlString = "select * from TADuties where courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber = " + AddQuotes(sectionNumber) + "and division = " + AddQuotes(division) + " order by TAID ASC, startTime ASC, endTime ASC";
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next()) {
			TADutiesInfo info = new TADutiesInfo();
	
			info.TAID = rs.getInt("TAID");
			info.COGSName = rs.getString("COGSName");
			info.division = rs.getString("division");
			info.groupnumber = rs.getString("groupNumber");
			info.studentID = rs.getInt("StudentID");
			info.question = rs.getString("question");
			info.starttime = rs.getString("StartTime");
			info.endtime = rs.getString("EndTime");
			info.rooM = rs.getString("room");
			list.add(info);
		}
				
		return list;
		
	}
	
	public ArrayList<TADutiesInfo> GetTADuties(int TAID, String courseNumber, String sectionNumber, String status) throws SQLException{
		
		ArrayList<TADutiesInfo> list = new ArrayList<TADutiesInfo>();
		
		
		//System.out.println(status);
		Statement stmt = connection.createStatement();

		//Grab all the COGSNames
		String sqlString = "select COGSName, question, division from TADuties where courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber = " + AddQuotes(sectionNumber) + "and status = " + AddQuotes(status) + "and TAID= " + TAID + " group by COGSName, division";
		
		//System.out.println(sqlString);
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next()) {
			TADutiesInfo info = new TADutiesInfo();
			info.COGSName = rs.getString("COGSName");
			info.question = rs.getString("question");
			info.division = rs.getString("division");
			list.add(info);
		}

		//Grab all the studentinfo
		for(TADutiesInfo tInfo: list)
		{
			ArrayList<String> firstnames = new ArrayList<String>();
			ArrayList<String> lastnames = new ArrayList<String>();
			ArrayList<String> groupNumbers = new ArrayList<String>(); 
			ArrayList<String> rooms = new ArrayList<String>();
			ArrayList<String>  StartTimes = new ArrayList<String>();
			ArrayList<String> EndTimes = new ArrayList<String>();
			
			sqlString = "select Student.firstname, Student.lastname,  " + " TAduties.groupNumber, " +
					" TAduties.room, TAduties.StartTime, TAduties.EndTime from TAduties inner join " +
					" Student on TAduties.studentID = Student.studentID " +
							 " where courseNumber = " + AddQuotes(courseNumber) +
							 " and TAID = " + TAID +
							 " and sectionNumber = " + AddQuotes(sectionNumber) + " and COGSName = " + AddQuotes(tInfo.COGSName) +
							 " and division = " + AddQuotes(tInfo.division) + " and status= " + AddQuotes(status) + " order by startTime ASC, endTime ASC, room ASC";
					
			System.out.println(sqlString);
			rs = stmt.executeQuery(sqlString);
			
			 while(rs.next()){
				 
				 firstnames.add(rs.getString("firstname"));
				 lastnames.add(rs.getString("lastname"));
				 groupNumbers.add(rs.getString("groupNumber"));
				 rooms.add(rs.getString("room"));
				 StartTimes.add(rs.getString("StartTime"));
				 EndTimes.add(rs.getString("EndTime"));
				 System.out.println("Group Numbers: " +rs.getString("groupNumber") );
				// System.out.println("first Names: " + rs.getString("firstname"));
			 }
			tInfo.firstNames = firstnames;
			tInfo.lastNames = lastnames;
			tInfo.groupNumbers = groupNumbers;
			tInfo.rooms = rooms;
			tInfo.StartTimes = StartTimes;
			tInfo.EndTimes = EndTimes;
		}
		
		return list;	
	}

	public ArrayList<String> GetDivisions(String courseNumber, String sectionNumber) throws SQLException {
		ArrayList<String> list = new ArrayList<String>();
		Statement stmt = connection.createStatement();

		//Grab all the COGSNames
		String sqlString = "select division from TADuties where courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber = " + AddQuotes(sectionNumber) + " group by division ASC";
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			list.add(rs.getString("division"));
		}
		
		return list;
	}
}